<?php
/**
 * WebboardController.php
 * WebboardController
 * @category classes
 *
 * @author AonzZung
 * @copyright AonzZung
 * @license http://www.opensource.org/licenses/osl-3.0.php Open-source licence 3.0
 * @version 0.1
 *
 *	@see
 * - webboard.tpl
 * - webboard.php
 * - WebboardController.php
 */

class Webboard extends ObjectModel
{
	protected $table = 'webboard';
	protected $identifier = 'topic_id';

	/* DB Field Attributes */
	public $parent_topic_id;

	//public $topic_id;

	public $topic_title;

	public $topic_content;

	public $author;

	public $author_id=0;
	//public $post_time;

	public $mode;

	protected $fieldsRequired = array('topic_content','author');
	protected $fieldsSize = array('topic_content' => 1000,'author' => 100, 'topic_title' => 100);

	public static function getTopics( $selectedPage, $topicCountPerPage)
	{
		$result = Db::getInstance()->ExecuteS('
		SELECT w1.*, COUNT( w2.topic_id ) as num_reply, c.email as email, IFNULL(LEFT(c.email,INSTR(c.email,\'@\')-1),\'guest\') as author_username
		FROM  '._DB_PREFIX_.'webboard w1
		LEFT JOIN '._DB_PREFIX_.'webboard w2 ON w2.parent_topic_id = w1.topic_id AND w2.`del` = \'N\'
		LEFT JOIN '._DB_PREFIX_.'customer c ON w1.author_id = c.id_customer
		WHERE w1.`parent_topic_id` = 0 AND w1.`del` = \'N\'
		GROUP BY w1.topic_id
		ORDER BY w1.pin DESC, w1.`post_time` DESC
		LIMIT '.((($selectedPage-1)*$topicCountPerPage)).', '.$topicCountPerPage
		);		
		return $result;
		
	}
	public static function getTopic( $id )
	{
		$result = Db::getInstance()->ExecuteS('
		SELECT w.*, IFNULL(LEFT(c.email,INSTR(c.email,\'@\')-1),\'guest\') as author_username, c.date_add as join_date, c.email as email
		FROM  '._DB_PREFIX_.'webboard w
		LEFT JOIN '._DB_PREFIX_.'customer c ON w.author_id = c.id_customer
		WHERE w.`parent_topic_id` = 0 AND w.`del` = \'N\'
		AND w.topic_id = '.$id
		);
		return $result;

	}

	public static function getTotalTopicCount( $id )
	{
		$result = Db::getInstance()->getValue('
		SELECT COUNT(w.parent_topic_id)
		FROM  '._DB_PREFIX_.'webboard w
		WHERE w.`parent_topic_id` = 0 AND w.`del` = \'N\''
		);
		return $result;

	}

	public static function getTopicTitle( $topic_id )
	{
		$result = Db::getInstance()->getValue('
		SELECT w.topic_title
		FROM  '._DB_PREFIX_.'webboard w
		WHERE w.`parent_topic_id` = 0
		AND w.topic_id = '.$topic_id.' AND w.`del` = \'N\''
		);
		return $result;

	}

	public static function getReplies( $topic_id,$selectedPage, $replyCountPerPage )
	{
		$result = Db::getInstance()->ExecuteS('
		SELECT w.*, IFNULL(LEFT(c.email,INSTR(c.email,\'@\')-1),\'guest\') as author_username, c.date_add as join_date, c.email as email
		FROM  '._DB_PREFIX_.'webboard w
		LEFT JOIN '._DB_PREFIX_.'customer c ON w.author_id = c.id_customer
		WHERE w.`parent_topic_id` = '.$topic_id.' AND w.`del` = \'N\'
		ORDER BY w.`post_time` ASC
		LIMIT '.((($selectedPage-1)*$replyCountPerPage)).', '.$replyCountPerPage
		);
		return $result;
	}

	public static function getTotalReplyCount( $topic_id )
	{
		$result = Db::getInstance()->getValue('
		SELECT COUNT(w.parent_topic_id)
		FROM  '._DB_PREFIX_.'webboard w
		WHERE w.`parent_topic_id` = '.$topic_id.' AND w.`del` = \'N\' 
		ORDER BY w.`post_time` DESC'
		);
		return $result;
	}

	/**
	 * Append ip of the client viewing the topic as a comma-delimited in the views field. 
	 * @param int $topic_id
	 * @param string $client_ip_addr client ip address.
	 * @return void
	 */
	public static function countView( $topic_id, $client_ip_addr )
	{
		//Check whether ip is exist or not.
		$ipExist = Db::getInstance()->getValue('
		SELECT count(topic_id) 
		FROM '._DB_PREFIX_.'webboard
		WHERE topic_id='.$topic_id.' and views LIKE \'%'.$client_ip_addr.'%\'
		');
		
		//Add ip if not exist
		if( intval($ipExist) == 0 )
		{		
			Db::getInstance()->Execute('
			UPDATE '._DB_PREFIX_.'webboard
			SET views=CONCAT(views, \''.$client_ip_addr.',\')
			WHERE topic_id='.$topic_id);			
		}
	}
	
	public static function getViewCount($topic_id)
	{
		$view_ip = Db::getInstance()->getValue('
		SELECT views 
		FROM '._DB_PREFIX_.'webboard
		WHERE topic_id='.$topic_id);
		
		$ip_arr = split(',',$view_ip);
		return count($ip_arr);
	}
	
	/**
	 * Add a new topic or a new reply
	 * @return true if succeeded.
	 */
	public function add()
	{		
		// set the default timezone to Bangkok.
		date_default_timezone_set('Asia/Bangkok');

		parent::validateFields();
		$row = array('topic_content' => pSQL($this->topic_content),
					 'author' => pSQL($this->author),
					 'author_id' => pSQL($this->author_id),	
					 'post_time' => date('Y-m-d H:i:s')
		);
		
		if($this->mode=="TOPIC")
		{
			if(isset($this->parent_topic_id))
			{
				$row['parent_topic_id'] = $this->parent_topic_id;
			}
			else
			{
				return false;
			}
		}
		else if($this->mode=="BOARD")
		{
			if(isset($this->topic_title) && !empty($this->topic_title))
			{
				$row['topic_title'] = pSQL($this->topic_title);
			}
			else
			{
				return false;
			}
		}
		
		return Db::getInstance()->AutoExecute(_DB_PREFIX_.'webboard', $row, 'INSERT');
	}
	
	public static function deleteTopic( $topic_id )
	{
		return Db::getInstance()->Execute('
			UPDATE '._DB_PREFIX_.'webboard
			SET del=\'Y\'
			WHERE topic_id='.$topic_id.' OR parent_topic_id='.$topic_id);
			
	}
	
	public static function pinTopic( $topic_id , $action)
	{
		$query_str = '
			UPDATE '._DB_PREFIX_.'webboard ';
		if ( $action == 'pin' )
			$query_str = $query_str.'SET pin=\'Y\' ';
		else if ( $action == 'unpin' )
			$query_str = $query_str.'SET pin=\'N\' ';	
		$query_str = $query_str.'WHERE topic_id='.$topic_id.' OR parent_topic_id='.$topic_id;
		//print_r($query_str);
		return Db::getInstance()->Execute($query_str);	
	}
	
	public static function checkTopicIdExist($topic_id)
	{
		return Db::getInstance()->getValue('
		SELECT 1 
		FROM '._DB_PREFIX_.'webboard
		WHERE topic_id='.$topic_id);
	}
}
?>